﻿-- =============================================
-- Author:		Taylor Chase
-- Create date: May 19 2016
-- Description:	
-- =============================================
CREATE FUNCTION [dbo].[DataConversion_AdmissionsProcess] 
(

)
RETURNS 
@JSONtoWrite TABLE (JSONvalue nvarchar(MAX),EntityID int,JSONvalue2 nvarchar(MAX),WriteType nvarchar(100))
AS
BEGIN

	INSERT INTO @JSONtoWrite(JSONvalue,EntityID,WriteType)
	SELECT '{"ShortRelationshipName":"ADMCOMP","Entity2ID":"16699"'
			+ ',"Entity1ID":"' + CAST(AP.Entity2ID AS NVARCHAR) + '"'
			+ ',"StartDate":"' + CAST(ADM.AcceptedDate AS NVARCHAR) + '"'
			+'}' AS JSONvalue,P.EntityID AS EntityID,'EntityRelationship' AS WriteType
	--SELECT * 
	FROM 
	-- Get admissions records that have a valid application date.  This will prevent nulls from sneaking into the system.
	OPENQUERY(STUDDATA97,'SELECT SAI.*,ApplicationDate,[First Year At RVA] AS FYAR,[First Term At RVA] AS FTAR 
								FROM [Student Admissions Info] AS SAI INNER JOIN Students AS S ON 
								SAI.StudentID = S.StudentID WHERE NOT ApplicationDate IS NULL 
								AND SAI.Accepted = -1') AS ADM
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL
									 @NOSQL = ''SELECT * FROM {Person}''') AS P
									  ON ADM.StudentID = P.PersonID
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL
									 @NOSQL = ''SELECT * FROM {Applied}''') AS AP
									  ON AP.Entity1ID = P.EntityID
	LEFT JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL
									 @NOSQL = ''SELECT * FROM {ADMCOMP}''') AS AC
									  ON AP.Entity2ID = AC.Entity1ID AND AC.Entity2ID = 16699
	WHERE AC.ShortRelationshipName IS NULL
	

	--PRINT 'Admissions Process Steps - ParentConfirmation'
	INSERT INTO @JSONtoWrite(JSONvalue,EntityID,WriteType)
	SELECT '{"ShortRelationshipName":"ADMCOMP","Entity2ID":"16702"'
			+ ',"Entity1ID":"' + CAST(AP.Entity2ID AS NVARCHAR) + '"'
			+ ',"StartDate":"' + CAST(ADM.ParentConfirmationDate AS NVARCHAR) + '"'
			+'}' AS JSONvalue,P.EntityID AS EntityID,'EntityRelationship' AS WriteType
	--SELECT * 
	FROM 
	-- Get admissions records that have a valid application date.  This will prevent nulls from sneaking into the system.
	OPENQUERY(STUDDATA97,'SELECT SAI.*,ApplicationDate,[First Year At RVA] AS FYAR,[First Term At RVA] AS FTAR 
								FROM [Student Admissions Info] AS SAI INNER JOIN Students AS S ON 
								SAI.StudentID = S.StudentID WHERE NOT ApplicationDate IS NULL 
								AND SAI.ParentConfirmation = -1') AS ADM
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL
									 @NOSQL = ''SELECT * FROM {Person}''') AS P
									  ON ADM.StudentID = P.PersonID
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL
									 @NOSQL = ''SELECT * FROM {Applied}''') AS AP
									  ON AP.Entity1ID = P.EntityID
	LEFT JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL
									 @NOSQL = ''SELECT * FROM {ADMCOMP}''') AS AC
									  ON AP.Entity2ID = AC.Entity1ID AND AC.Entity2ID = 16702
	WHERE AC.ShortRelationshipName IS NULL

	--PRINT 'Admissions Process Steps - DepositPaid'
	INSERT INTO @JSONtoWrite(JSONvalue,EntityID,WriteType)
	SELECT '{"ShortRelationshipName":"ADMCOMP","Entity2ID":"16703"'
			+ ',"Entity1ID":"' + CAST(AP.Entity2ID AS NVARCHAR) + '"'
			+ ',"StartDate":"' + CAST(ADM.DepositPaidDate AS NVARCHAR) + '"'
			+'}' AS JSONvalue,P.EntityID AS EntityID,'EntityRelationship' AS WriteType
	--SELECT * 
	FROM 
	-- Get admissions records that have a valid application date.  This will prevent nulls from sneaking into the system.
	OPENQUERY(STUDDATA97,'SELECT SAI.*,ApplicationDate,[First Year At RVA] AS FYAR,[First Term At RVA] AS FTAR 
								FROM [Student Admissions Info] AS SAI INNER JOIN Students AS S ON 
								SAI.StudentID = S.StudentID WHERE NOT ApplicationDate IS NULL 
								AND SAI.DepositPaid = -1') AS ADM
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL
									 @NOSQL = ''SELECT * FROM {Person}''') AS P
									  ON ADM.StudentID = P.PersonID
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL
									 @NOSQL = ''SELECT * FROM {Applied}''') AS AP
									  ON AP.Entity1ID = P.EntityID
	LEFT JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL
									 @NOSQL = ''SELECT * FROM {ADMCOMP}''') AS AC
									  ON AP.Entity2ID = AC.Entity1ID AND AC.Entity2ID = 16703
	WHERE AC.ShortRelationshipName IS NULL

	--PRINT 'Admissions Process Steps - ITComplete'
	INSERT INTO @JSONtoWrite(JSONvalue,EntityID,WriteType)
	SELECT '{"ShortRelationshipName":"ADMCOMP","Entity2ID":"16704"'
			+ ',"Entity1ID":"' + CAST(AP.Entity2ID AS NVARCHAR) + '"'
			+ ',"StartDate":"' + CAST(ADM.ITCompleteDate AS NVARCHAR) + '"'
			+'}' AS JSONvalue,P.EntityID AS EntityID,'EntityRelationship' AS WriteType
	--SELECT * 
	FROM 
	-- Get admissions records that have a valid application date.  This will prevent nulls from sneaking into the system.
	OPENQUERY(STUDDATA97,'SELECT SAI.*,ApplicationDate,[First Year At RVA] AS FYAR,[First Term At RVA] AS FTAR 
								FROM [Student Admissions Info] AS SAI INNER JOIN Students AS S ON 
								SAI.StudentID = S.StudentID WHERE NOT ApplicationDate IS NULL 
								AND SAI.ITComplete = -1') AS ADM
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL
									 @NOSQL = ''SELECT * FROM {Person}''') AS P
									  ON ADM.StudentID = P.PersonID
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL
									 @NOSQL = ''SELECT * FROM {Applied}''') AS AP
									  ON AP.Entity1ID = P.EntityID
	LEFT JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL
									 @NOSQL = ''SELECT * FROM {ADMCOMP}''') AS AC
									  ON AP.Entity2ID = AC.Entity1ID AND AC.Entity2ID = 16704
	WHERE AC.ShortRelationshipName IS NULL

	--PRINT 'Admissions Process Steps - EnrollmentFormsComplete'
	INSERT INTO @JSONtoWrite(JSONvalue,EntityID,WriteType)
	SELECT '{"ShortRelationshipName":"ADMCOMP","Entity2ID":"16707"'
			+ ',"Entity1ID":"' + CAST(AP.Entity2ID AS NVARCHAR) + '"'
			+ ',"StartDate":"' + CAST(ADM.EnrollmentFormsCompleteDate AS NVARCHAR) + '"'
			+'}' AS JSONvalue,P.EntityID AS EntityID,'EntityRelationship' AS WriteType
	--SELECT * 
	FROM 
	-- Get admissions records that have a valid application date.  This will prevent nulls from sneaking into the system.
	OPENQUERY(STUDDATA97,'SELECT SAI.*,ApplicationDate,[First Year At RVA] AS FYAR,[First Term At RVA] AS FTAR 
								FROM [Student Admissions Info] AS SAI INNER JOIN Students AS S ON 
								SAI.StudentID = S.StudentID WHERE NOT ApplicationDate IS NULL 
								AND SAI.EnrollmentFormsComplete = -1') AS ADM
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL
									 @NOSQL = ''SELECT * FROM {Person}''') AS P
									  ON ADM.StudentID = P.PersonID
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL
									 @NOSQL = ''SELECT * FROM {Applied}''') AS AP
									  ON AP.Entity1ID = P.EntityID
	LEFT JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL
									 @NOSQL = ''SELECT * FROM {ADMCOMP}''') AS AC
									  ON AP.Entity2ID = AC.Entity1ID AND AC.Entity2ID = 16707
	WHERE AC.ShortRelationshipName IS NULL

	--PRINT 'Admissions Process Steps - AppFeePaid'
	INSERT INTO @JSONtoWrite(JSONvalue,EntityID,WriteType)
	SELECT '{"ShortRelationshipName":"ADMCOMP","Entity2ID":"16708"'
			+ ',"Entity1ID":"' + CAST(AP.Entity2ID AS NVARCHAR) + '"'
			+ ',"StartDate":"' + CAST(COALESCE(ADM.AppFeePaidDate,ADM.ApplicationDate) AS NVARCHAR) + '"'
			+'}' AS JSONvalue,P.EntityID AS EntityID,'EntityRelationship' AS WriteType
	--SELECT * 
	FROM 
	-- Get admissions records that have a valid application date.  This will prevent nulls from sneaking into the system.
	OPENQUERY(STUDDATA97,'SELECT SAI.*,ApplicationDate,[First Year At RVA] AS FYAR,[First Term At RVA] AS FTAR 
								FROM [Student Admissions Info] AS SAI INNER JOIN Students AS S ON 
								SAI.StudentID = S.StudentID WHERE NOT ApplicationDate IS NULL 
								AND SAI.AppFeePaid = -1') AS ADM
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL
									 @NOSQL = ''SELECT * FROM {Person}''') AS P
									  ON ADM.StudentID = P.PersonID
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL
									 @NOSQL = ''SELECT * FROM {Applied}''') AS AP
									  ON AP.Entity1ID = P.EntityID
	LEFT JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL
									 @NOSQL = ''SELECT * FROM {ADMCOMP}''') AS AC
									  ON AP.Entity2ID = AC.Entity1ID AND AC.Entity2ID = 16708
	WHERE AC.ShortRelationshipName IS NULL



	RETURN 
END